
ALTER             FUNCTION fn_ct_mkg_load_projected_actual_forecast_details(

@CUSTOMER VARCHAR(100),
@PRODUCT VARCHAR(100),
@SALES_PERSON VARCHAR(100),
@MONTH_FROM INT,
@MONTH_TO INT,
@YEAR INT,
@END_USER_INPUT VARCHAR(100))


RETURNS @FN_RESULTS TABLE(CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100),  SALES_PERSON VARCHAR(100), END_USER VARCHAR(100), MONTH INT, YEAR INT, SF_QUANTITY INT , MISC VARCHAR(15)  )


AS
BEGIN

--Added by Ganddhi on 2nd Oct 2004
DECLARE @MISC_RESULTS TABLE(CUSTOMER VARCHAR(100), PRODUCT VARCHAR(100),  SALES_PERSON VARCHAR(100), END_USER VARCHAR(100), MONTH INT, YEAR INT, MISC_QUANTITY INT , MISC VARCHAR(15)  )
-- Ends here - Gandhi


	IF RTRIM(@MONTH_FROM) = -1
	BEGIN
		SET @MONTH_FROM = 0
	END

	IF RTRIM(@MONTH_TO) = -1
	BEGIN
		SET @MONTH_TO = 11
	END
	
	DECLARE @TO_MONTH INT	
	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 

	
	DECLARE @ROW_COUNT INT	
	DECLARE @MISC_ROW_COUNT INT
	
	DECLARE @SF_CUSTOMER VARCHAR(100)
	DECLARE @SF_PRODUCT VARCHAR(50)
	DECLARE @SF_SALES_PERSON VARCHAR(50)
	DECLARE @SF_END_USER VARCHAR(100)
	DECLARE @SF_MONTH INT
	DECLARE @SF_YEAR INT
	DECLARE @SF_QUANTITY INT
	DECLARE @SF_OLD_QUANTITY INT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100) 

	DECLARE @MISC_CUSTOMER VARCHAR(100)
	DECLARE @MISC_PRODUCT VARCHAR(50)
	DECLARE @MISC_SALES_PERSON VARCHAR(50)
	DECLARE @MISC_END_USER VARCHAR(100)
	DECLARE @MISC_MONTH INT
	DECLARE @MISC_YEAR INT
	DECLARE @MISC_QUANTITY INT
	DECLARE @MISC_OLD_QUANTITY INT
	DECLARE @MISC_MKTG_PART_NUMBER VARCHAR(100) 
	
	DECLARE @TEMP_CURSOR CURSOR 
	DECLARE @MISC_CURSOR CURSOR -- Added By Gandhi on 2nd Oct 2004
			
	IF @CUSTOMER <> '0' AND @PRODUCT <> '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT <> '0'
	BEGIN
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER_INPUT
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004

	END

	ELSE IF @CUSTOMER <> '0' AND @PRODUCT = '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT = '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE CUSTOMER = @CUSTOMER 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END
	
	ELSE IF @CUSTOMER = '0' AND @PRODUCT <> '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT = '0'
	BEGIN
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE PRODUCT = @PRODUCT 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004

	END
	
	ELSE IF @CUSTOMER = '0' AND @PRODUCT = '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT = '0'
	BEGIN
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
			
	END	

	ELSE IF @CUSTOMER = '0' AND @PRODUCT = '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE END_USER = @END_USER_INPUT 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END
	
	ELSE IF @CUSTOMER <> '0' AND @PRODUCT <> '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT = '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE PRODUCT = @PRODUCT AND CUSTOMER = @CUSTOMER 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END
	
	ELSE IF @CUSTOMER <> '0' AND @PRODUCT = '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT = '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE CUSTOMER = @CUSTOMER AND SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END

	ELSE IF @CUSTOMER <> '0' AND @PRODUCT = '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE CUSTOMER = @CUSTOMER AND END_USER = @END_USER_INPUT 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END
	
	ELSE IF @CUSTOMER = '0' AND @PRODUCT <> '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT = '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE PRODUCT = @PRODUCT AND SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END

	ELSE IF @CUSTOMER = '0' AND @PRODUCT <> '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE PRODUCT = @PRODUCT AND END_USER = @END_USER_INPUT 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END

	ELSE IF @CUSTOMER = '0' AND @PRODUCT <> '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE PRODUCT = @PRODUCT AND END_USER = @END_USER_INPUT AND SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004
		
 	END

	ELSE IF @CUSTOMER = '0' AND @PRODUCT = '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE END_USER = @END_USER_INPUT AND SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004		
 	END

	ELSE IF @CUSTOMER <> '0' AND @PRODUCT = '0' AND @SALES_PERSON <> '0' AND @END_USER_INPUT <> '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE CUSTOMER = @CUSTOMER AND END_USER = @END_USER_INPUT AND SALES_PERSON = @SALES_PERSON 
		AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004		
 	END

	ELSE IF @CUSTOMER = '0' AND @PRODUCT = '0' AND @SALES_PERSON = '0' AND @END_USER_INPUT = '0'
	BEGIN	
		SET @TEMP_CURSOR = CURSOR FOR
		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SF_MONTH[MONTH],
		SF_YEAR[YEAR], SF_QTY[QUANTITY]
		FROM SALES_FORECAST_MASTER 
		WHERE ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) -- Modified by Gandhi on 14th Sep 2004		
 	END
	


		OPEN @TEMP_CURSOR 
			FETCH NEXT FROM @TEMP_CURSOR
			INTO @SF_CUSTOMER, @SF_PRODUCT, @SF_SALES_PERSON, @SF_END_USER, @SF_MONTH, @SF_YEAR, @SF_QUANTITY				
			WHILE @@FETCH_STATUS = 0
			BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS
					WHERE CUSTOMER = @SF_CUSTOMER AND PRODUCT = @SF_PRODUCT AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR AND SALES_PERSON = @SF_SALES_PERSON AND END_USER = @SF_END_USER
					
						
						IF ( @ROW_COUNT = 0 )
					     	BEGIN
							INSERT INTO @FN_RESULTS VALUES( @SF_CUSTOMER, @SF_PRODUCT, @SF_SALES_PERSON, @SF_END_USER, @SF_MONTH, @SF_YEAR, @SF_QUANTITY, '' )
						END
						ELSE
						BEGIN
							SELECT @SF_OLD_QUANTITY = SF_QUANTITY FROM @FN_RESULTS
							WHERE CUSTOMER = @SF_CUSTOMER AND PRODUCT = @SF_PRODUCT AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR AND SALES_PERSON = @SF_SALES_PERSON AND END_USER = @SF_END_USER
							
							UPDATE @FN_RESULTS SET SF_QUANTITY = (@SF_QUANTITY + @SF_OLD_QUANTITY)
							WHERE CUSTOMER = @SF_CUSTOMER AND PRODUCT = @SF_PRODUCT AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR AND SALES_PERSON = @SF_SALES_PERSON AND END_USER = @SF_END_USER
						END
	
										
					SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @SF_PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
					UPDATE @FN_RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE CUSTOMER = @SF_CUSTOMER AND PRODUCT = @SF_PRODUCT AND MONTH = @SF_MONTH AND YEAR = @SF_YEAR AND SALES_PERSON = @SF_SALES_PERSON AND END_USER = @SF_END_USER

					
					FETCH NEXT FROM @TEMP_CURSOR
					INTO @SF_CUSTOMER, @SF_PRODUCT, @SF_SALES_PERSON, @SF_END_USER, @SF_MONTH, @SF_YEAR, @SF_QUANTITY			
			END	
										
																
		CLOSE @TEMP_CURSOR
		DEALLOCATE @TEMP_CURSOR

							
				-- Commented by  Gandhi on 2nd Oct 2004	
				     /*  INSERT INTO @FN_RESULTS SELECT CUSTOMER, PRODUCT, SALES_PERSON [SALES PERSON ], END_USER [END USER], SF_MONTH [MONTH], SF_YEAR [YEAR] , SUM(SF_QTY) QTY, 'MISC'  FROM MISC_SALES_FORECAST_MASTER 
				       WHERE ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH )
				       GROUP BY CUSTOMER,PRODUCT,SALES_PERSON,END_USER,SF_MONTH,SF_YEAR */
		
		-- Added by Gandhi on 2nd Oct 2004
		SET @MISC_CURSOR = CURSOR FOR 	SELECT CUSTOMER, PRODUCT, SALES_PERSON [SALES PERSON ], END_USER [END USER], SF_MONTH [MONTH], SF_YEAR [YEAR] , SF_QTY [QTY] FROM MISC_SALES_FORECAST_MASTER 
				       WHERE ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH )
				      
		-- Modified by Gandhi on 7th Oct 2004
		OPEN @MISC_CURSOR 
			FETCH NEXT FROM @MISC_CURSOR
			INTO @MISC_CUSTOMER, @MISC_PRODUCT, @MISC_SALES_PERSON, @MISC_END_USER, @MISC_MONTH, @MISC_YEAR, @MISC_QUANTITY				
			WHILE @@FETCH_STATUS = 0
			BEGIN
					SELECT @MISC_ROW_COUNT = COUNT(*) FROM @MISC_RESULTS
					WHERE CUSTOMER = @MISC_CUSTOMER AND PRODUCT = @MISC_PRODUCT AND MONTH = @MISC_MONTH AND YEAR = @MISC_YEAR AND SALES_PERSON = @MISC_SALES_PERSON AND END_USER = @MISC_END_USER
					
						
						IF ( @MISC_ROW_COUNT = 0 )
					     	BEGIN
							INSERT INTO @MISC_RESULTS VALUES( @MISC_CUSTOMER, @MISC_PRODUCT, @MISC_SALES_PERSON, @MISC_END_USER, @MISC_MONTH, @MISC_YEAR, @MISC_QUANTITY, 'MISC' )
						END
						ELSE
						BEGIN
							SELECT @MISC_OLD_QUANTITY = MISC_QUANTITY FROM @MISC_RESULTS
							WHERE CUSTOMER = @MISC_CUSTOMER AND PRODUCT = @MISC_PRODUCT AND MONTH = @MISC_MONTH AND YEAR = @MISC_YEAR AND SALES_PERSON = @MISC_SALES_PERSON AND END_USER = @MISC_END_USER
							
							UPDATE @MISC_RESULTS SET MISC_QUANTITY = (@MISC_QUANTITY + @MISC_OLD_QUANTITY)
							WHERE CUSTOMER = @MISC_CUSTOMER AND PRODUCT = @MISC_PRODUCT AND MONTH = @MISC_MONTH AND YEAR = @MISC_YEAR AND SALES_PERSON = @MISC_SALES_PERSON AND END_USER = @MISC_END_USER
						END
	
										
					SELECT @MISC_MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @MISC_PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  

					IF( @MISC_MKTG_PART_NUMBER IS NULL )
					BEGIN
						UPDATE @MISC_RESULTS SET PRODUCT =  @MISC_PRODUCT WHERE CUSTOMER = @MISC_CUSTOMER AND PRODUCT = @MISC_PRODUCT AND MONTH = @MISC_MONTH AND YEAR = @MISC_YEAR AND SALES_PERSON = @MISC_SALES_PERSON AND END_USER = @MISC_END_USER
					END
					ELSE
					BEGIN
						UPDATE @MISC_RESULTS SET PRODUCT =  @MISC_MKTG_PART_NUMBER WHERE CUSTOMER = @MISC_CUSTOMER AND PRODUCT = @MISC_PRODUCT AND MONTH = @MISC_MONTH AND YEAR = @MISC_YEAR AND SALES_PERSON = @MISC_SALES_PERSON AND END_USER = @MISC_END_USER
					END
					
					SET @MISC_MKTG_PART_NUMBER = NULL				
					
					FETCH NEXT FROM @MISC_CURSOR
					INTO @MISC_CUSTOMER, @MISC_PRODUCT, @MISC_SALES_PERSON, @MISC_END_USER, @MISC_MONTH, @MISC_YEAR, @MISC_QUANTITY			
			END	
					INSERT INTO @FN_RESULTS SELECT * FROM @MISC_RESULTS					
																
		CLOSE @MISC_CURSOR
		DEALLOCATE @MISC_CURSOR
		-- Modification ends here - Gandhi
		

			-- Commented by  Gandhi on 24th Sep 2004
					/*INSERT INTO @MISC_RESULTS SELECT CUSTOMER, P_Marketing_Part_Number[PRODUCT], SALES_PERSON [SALES PERSON ], END_USER [END USER], SF_MONTH [MONTH], SF_YEAR [YEAR] , SUM(SF_QTY) QTY, 'MISC'  FROM MISC_SALES_FORECAST_MASTER MFM
					INNER JOIN Product P ON P.Part_Number = MFM.PRODUCT
			 		INNER JOIN Process_Step PS ON PS.ProcessStep_ID = P.ProcessStep_ID
			 		WHERE PS.PS_Type_Name LIKE 'Finished Goods' AND ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH )
				        GROUP BY CUSTOMER,P_Marketing_Part_Number,SALES_PERSON,END_USER,SF_MONTH,SF_YEAR

					INSERT INTO @MISC_RESULTS SELECT CUSTOMER, PRODUCT, SALES_PERSON [SALES PERSON ], END_USER [END USER], SF_MONTH [MONTH], SF_YEAR [YEAR] , SUM(SF_QTY) QTY, 'MISC'  FROM MISC_SALES_FORECAST_MASTER 
				       	WHERE ( SF_MONTH >= @MONTH_FROM AND SF_YEAR = @YEAR AND SF_MONTH <= @TO_MONTH ) AND
					PRODUCT NOT IN (SELECT PRODUCT FROM @MISC_RESULTS)
				       	GROUP BY CUSTOMER,PRODUCT,SALES_PERSON,END_USER,SF_MONTH,SF_YEAR

					INSERT INTO @FN_RESULTS SELECT * FROM @MISC_RESULTS*/
					-- Ends here - Gandhi
 
   RETURN

END



